﻿SELECT TOP 1 
	A.ELU_ID, 
	B.FULL_NM, 
	B.BAN, 
	B.CLOC, 
	B.CUST_SUB_TYPE, 
	B.UVERSE_RAAID,
	B.IPTV_PACKAGE, 
	B.IPTV_START_DT, 
	B.RCVR_CNT, 
	B.DVR_CNT, 
	B.HD_IND, 
	B.HSIA_SPEED, 
	B.HSIA_START_DT, 
	B.HSIA_STATIC_IP_IND, 
	B.VOIP_PLAN, 
	B.VOIP_START_DT, 
	B.VOIP_LINE_CNT, 
	CASE WHEN C.ELU_ID IS NOT NULL THEN 'GREEN' ELSE 'RED' END IPTV_AVAIL_CD, 
	CASE WHEN C.TECH_DNSTRM_BNDWTH>13.0 THEN 'GREEN' ELSE 'RED' END HSIA_AVAIL_CD, 
	CASE WHEN HSIA_AVAIL_CD='GREEN' OR IPTV_AVAIL_CD='GREEN' THEN 'GREEN' ELSE 'RED' END VOIP_AVAIL_IND, 
	C.WIRE_CENTER, 
	C.LUID, 
	A.DA_CD, 
	'<br />'||A.STREET_NUM||' '||A.STREET_NAME||' '||COALESCE(A.UNIT_NUM,'')||'<br />'||A.CITY||', '||A.STATE_CD||' '||A.ZIP_1_5_CD "ADDRESS",
	A.STREET_NUM||' '||A.STREET_NAME||' '||COALESCE(A.UNIT_NUM,'') UNIT_ADDR
FROM 
	EDWEN.ACC_LIST 
	A 
	LEFT OUTER JOIN 
	SUNRISE.UVERSE_DATA 
	B 
	ON A.ELU_ID=B.OVALS_LUID 
	LEFT OUTER JOIN 
	SUNRISE_NB.SE_GREENS 
	C 
	ON A.ELU_ID=C.ELU_ID 
WHERE 
	A.PMASTER_ADDR_ID={0}
